﻿using Oracle.DataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Wicresoft.UnifyShow.Utility;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class PlanManagement : Basic, IPlanManagement
    {
        private string sqlDeleteInspectionType = "delete from InspectionType where ID=:id";
        //业务系统搜索 
        private string sqlSearchInspObject_Business = "select * from (select t.*,rownum rn from (select {0} from AST_BMC_AppSystem  where category='应用系统' and datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null) {1} )t) {2}";
        //服务器搜索
        private string sqlSearchInspObject_Server = "select * from (select t.*,rownum rn from (select {0} from AST_BMC_Hardware where type='服务器' and datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null) {1} )t) {2}";
        //网络设备搜索
        private string sqlSearchInspObject_Net = "select * from (select t.*,rownum rn from (select {0} from AST_BMC_Hardware where type='网络设备' and datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null) {1} )t) {2}";
        //安全设备搜索
        private string sqlSearchInspObject_Safety = "select * from (select t.*,rownum rn from (select {0} from AST_BMC_Hardware where type='安全设备' and datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null) {1} )t) {2}";
        //数据库搜索
        //备份搜索
        private string sqlSearchInspObject_Db = "select * from (select t.*,rownum rn from (select {0} from AST_BMC_SysSoftware where type='数据库' and datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null) {1} )t) {2}";
        //中间件搜索
        private string sqlSearchInspObject_Middle = "select * from (select t.*,rownum rn from (select {0} from AST_BMC_SysSoftware where type='中间件' and datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null) {1} )t) {2}";
        //存储搜索
        private string sqlSearchInspObject_Storage = "select * from (select t.*,rownum rn from (select {0} from AST_BMC_Hardware where type='存储设备' and datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null) {1} )t) {2}";
        //修改计划的启用状态
        private string sqlUpdateJobInvalid = "update job_x set Invalid=:Invalid where id=:JobID";
        //删除电子公告
        private string sqlDeleteAnnouncement = "update announcement set InValid = 0 where ID=:id";
        //更新公告数据
        private string sqlUpdateAnnouncement = "update announcement set Title=:Title,Content=:Content,BeginTime=:BeginTime,EndTime=:EndTime where id=:id";
        //删除文件
        private string sqlDeleteFile = "delete from TASKATTACHMENT where ID =:id";
        //获取所有品牌
        private string sqlGetVendorList = "select vendor from FJ_AST_Vendor where status=0";
        //获取数据库版本
        private string sqlGetDBVersionList = "select databaseandversion from FJ_AST_DatabaseAndVersion where databaseandversion!='无'";
        //获取中间件版本
        private string sqlGetMiddleVersionList = "select midsoftwareandversion from fj_ast_midsoftwareandversion where midsoftwareandversion!='无'";
        //获取所有版本
        private string sqlGetVersionList = "select VersionNumber from FJ_AST_Software_VersionNumber where status=0";
        //获取所有用途
        private string sqlGetUsingList = "select AppSystemName from AST_BMC_AppSystem where category='应用系统' and datasetid='BMC.ASSET' and (markasdeleted=0 or markasdeleted is null)";
        //删除组成员
        private string sqlDeleteGroupUser = "delete from plangroupuser where groupid=:groupid and userid=:userid";
        //获取计划任务组
        private string sqlGetGlobalAllGroup = "select * from (select t.*,rownum rn from (select {0} from plangroup  where Invalid=1 and {1} )t) {2}";
        //计划保存之后更新计划相关的文件
        private string sqlUpdateJobFile = "update taskattachment set relationid = :relationID where GID =:GID";
        //删除内置任务组的人员
        private string sqlDeleteInternalPlanGroupUser = "delete from plangroupuser where id in(select a.id from plangroupuser a left join plangroup b on a.groupid = b.id where b.grouptype =1)";
        //省局内置组人员初始化
        private string sqlInsertFJInternalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id from plangroup a, user_x b where a.groupname = '省局信息中心' and b. disabled!=1 and b.company = '福建省烟草专卖局' and b.department = '经济信息中心'";
        //厦门内置组人员初始化
        private string sqlInsertXMInetrnalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id  from plangroup a, user_x b where a.groupname = '厦门信息中心' and b. disabled!=1    and b.company = '厦门市烟草专卖局'   and b.department = '信息中心'";
        //福州内置组人员初始化
        private string sqlInsertFZHInetrnalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id  from plangroup a, user_x b where a.groupname = '福州信息中心' and b. disabled!=1    and b.company = '福州市烟草专卖局'   and b.department = '市局_信息中心'";
        //漳州内置组人员初始化
        private string sqlInsertZZHInternalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id from plangroup a, user_x b where a.groupname = '漳州信息中心' and b. disabled!=1 and b.company = '漳州市烟草专卖局'   and b.department = '市局_信息中心'";
        //泉州内置组人员初始化
        private string sqlInsertQZHInternalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id from plangroup a, user_x b where a.groupname = '泉州信息中心' and b. disabled!=1  and b.company = '泉州市烟草专卖局' and b.department = '市局_信息中心'";
        //莆田内置组人员初始化
        private string sqlInsertPTInternalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id from plangroup a, user_x b where a.groupname = '莆田信息中心' and b. disabled!=1  and b.company = '莆田市烟草专卖局' and b.department = '市局_信息中心'";
        //宁德内置组人员初始化
        private string sqlInsertNDInternalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id from plangroup a, user_x b where a.groupname = '宁德信息中心' and b. disabled!=1  and b.company = '宁德市烟草专卖局' and b.department = '信息中心'";
        //南平内置组人员初始化
        private string sqlInsertNPInternalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id from plangroup a, user_x b where a.groupname = '南平信息中心' and b. disabled!=1  and b.company = '南平市烟草专卖局' and b.department = '市局_信息中心'";
        //三明内置组人员初始化
        private string sqlInsertSMInternalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id from plangroup a, user_x b where a.groupname = '三明信息中心' and b. disabled!=1  and b.company = '三明市烟草专卖局' and b.department = '信息中心'";
        //龙岩内置组人员初始化
        private string sqlInsertLYInternalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id from plangroup a, user_x b where a.groupname = '龙岩信息中心' and b. disabled!=1  and b.company = '龙岩市烟草专卖局' and b.department = '市局_信息中心'";
        //海投内置组人员初始化
        private string sqlInsertHTInternalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id from plangroup a, user_x b where a.groupname = '海投办公室' and b. disabled!=1  and b.company = '福建烟草海晟投资管理有限公司' and b.department = '海晟投资_办公室'";
        //三明金叶内置组人员初始化
        private string sqlInsertSMJYInternalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id from plangroup a, user_x b where a.groupname = '三明金叶综合部' and b. disabled!=1  and b.company = '三明金叶复烤有限公司' and b.department = '综合部'";
        //进出口办公室内置组人员初始化
        private string sqlInsertJCKInternalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id from plangroup a, user_x b where a.groupname = '进出口办公室' and b. disabled!=1  and b.company = '中国烟草福建进出口公司' and b.department = '办公室'";
        //武夷烟叶内置组人员初始化
        private string sqlInsertWYYYInetrnalUser = "insert into plangroupuser (id,groupid,userid) select PLANGROUPUSER_SEQ.nextval, a.id, b.id from plangroup a, user_x b where a.groupname = '武夷烟叶技术中心' and b. disabled!=1  and b.company = '福建武夷烟叶有限公司' and b.department = '技术中心'";

        //todo zhaozhi 保存计划
        public bool SaveJob(ScheduleJob scheduleJob)
        {

            if (scheduleJob == null || scheduleJob.Job == null || scheduleJob.Cycle.Count <= 0) return false;

            OracleTransaction trans = oda.BeginTransaction();
            try
            {
                var id = ModelHelper<JobModel>.AddModel_By_QueryParams_Return_ID(scheduleJob.Job, new List<string>() { "JobOwnerName", "TaskUserName", "FileGID" }, oda);

                //计划周期
                foreach (var s in scheduleJob.Cycle)
                {
                    s.JobID = id;
                    var cycleID = ModelHelper<CycleModel>.AddModel_By_QueryParams_Return_ID(s, new List<string>() { }, oda);

                    //计划范围，任务范围
                    if ((scheduleJob.Job.JobType == 2 || scheduleJob.Job.JobType == 3) && !string.IsNullOrEmpty(scheduleJob.Job.JobRange))
                    {
                        var range_str = scheduleJob.Job.JobRange.Split(';');
                        foreach (var _r in range_str)
                        {
                            if (!string.IsNullOrWhiteSpace(_r))
                            {
                                var range = new PlanTaskScope()
                                {
                                    CycleId = cycleID,
                                    LocationID = decimal.Parse(_r)
                                };

                                ModelHelper<PlanTaskScope>.AddModel_By_QueryParams(range, new List<string>() { }, oda);
                            }
                        }

                    }
                }

                //电子公告
                if (scheduleJob.Job.HasBulletin == 1 && scheduleJob.Bulletin != null)
                {
                    scheduleJob.Bulletin.JobID = id;
                    ModelHelper<AnnouncementModel>.AddModel_By_QueryParams(scheduleJob.Bulletin, new List<string>() { "CreateByName" }, oda);
                }

                //一般计划的文件保存
                if (scheduleJob.Job.JobType == 3 && !string.IsNullOrWhiteSpace(scheduleJob.Job.FileGID))
                {
                    var files = GetFileListByGID(scheduleJob.Job.FileGID);

                    foreach (var file in files)
                    {
                        oda.ExecuteScalar(sqlUpdateJobFile, new List<OracleParameter>()
                        {
                            new OracleParameter("relationID", OracleDbType.Decimal, 100, id, ParameterDirection.Input),
                            new OracleParameter("GID", OracleDbType.NVarchar2, 100, scheduleJob.Job.FileGID, ParameterDirection.Input)
                        });
                    }
                }

                trans.Commit();
                return true;
            }
            catch (Exception e) { trans.Rollback(); return false; }

        }

        /// <summary>
        /// 检查计划名重复
        /// </summary>
        /// <param name="jobName"></param>
        /// <param name="expectID"></param>
        /// <returns></returns>
        public bool IsJobNameExisted(string jobName, decimal? expectID)
        {
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="JobName", OracleDbColumnType=OracleDbType.NVarchar2,
                        OracleDbColumnValue=jobName,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };

            if (expectID.HasValue)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "ID",
                    OracleDbColumnType = OracleDbType.Decimal,
                    OracleDbColumnValue = expectID.Value,
                    QueryOperator = QueryOperatorType.notEqual,
                    PredicateType = PredicateType.where
                });
            }

            var result = ModelHelper<JobModel>.GetModel_By_QueryParams(attParam, null, null, null, oda);

            return result.Any();
        }

        public ScheduleJob GetScheduleJobDetail(decimal ID)
        {
            var schedule = new ScheduleJob();

            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="ID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=ID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };

            var job = ModelHelper<JobModel>.GetModel_By_QueryParams(attParam, null, null, null, oda).FirstOrDefault();
            if (job != null)
            {
                schedule.Job = job;

                var cycleParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="JobID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=ID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };

                schedule.Cycle = ModelHelper<CycleModel>.GetModel_By_QueryParams(cycleParam, null, null, null, oda);

                schedule.Bulletin = ModelHelper<AnnouncementModel>.GetModel_By_QueryParams(cycleParam, null, null, null, oda).FirstOrDefault();
            }

            return schedule;
        }

        public TaskModel GetTaskDetail(decimal ID)
        {
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="ID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=ID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };

            return ModelHelper<TaskModel>.GetModel_By_QueryParams(attParam, null, null, null, oda).FirstOrDefault();

        }

        public List<PlanLocationOwner> GetSiteALLBranch()
        {
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="Alias", OracleDbColumnType=OracleDbType.Decimal,
                        QueryOperator=QueryOperatorType.isNotNull,PredicateType=PredicateType.where}
                    };

            return ModelHelper<PlanLocationOwner>.GetModel_By_QueryParams(attParam, null, null, null, oda);
        }

        public List<TaskAttachmentModel> GetFileList(decimal ID)
        {
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="RelationID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=ID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };

            return ModelHelper<TaskAttachmentModel>.GetModel_By_QueryParams(attParam, null, null, null, oda);
        }


        public List<TaskAttachmentModel> GetFileListByGID(string GID)
        {
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="GID", OracleDbColumnType=OracleDbType.NVarchar2,
                        OracleDbColumnValue=GID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };

            return ModelHelper<TaskAttachmentModel>.GetModel_By_QueryParams(attParam, null, null, null, oda);
        }

        public TaskAttachmentModel GetFileByID(int ID)
        {
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="ID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=ID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };

            return ModelHelper<TaskAttachmentModel>.GetModel_By_QueryParams(attParam, null, null, null, oda).FirstOrDefault();
        }

        public bool AddFile(TaskAttachmentModel file)
        {
            var row = ModelHelper<TaskAttachmentModel>.AddModel_By_QueryParams(file, new List<string>() { "UpLoadUserName" }, oda);

            return row == 1;
        }

        public bool DeleteFile(int FileID)
        {
            int result = oda.ExecuteCommand(sqlDeleteFile, new List<OracleParameter>()
                {   
                    new OracleParameter()
                    {
                        ParameterName="id",
                        OracleDbType = OracleDbType.Decimal,
                        Value = (decimal)FileID
                    }
                });

            return result == 1;
        }

        /// <summary>
        /// 更新任务
        /// </summary>
        /// <param name="task"></param>
        /// <param name="files"></param>
        /// <returns></returns>
        public bool SaveTask(TaskModel task, List<TaskAttachmentModel> files)
        {
            var oldTask = GetTaskDetail(task.ID);

            List<QueryParameter> attParam = new List<QueryParameter>() { 
                    new QueryParameter(){
                        OracleDbColumnName="ID", 
                        OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=task.ID,
                        QueryOperator=QueryOperatorType.equal,
                        PredicateType=PredicateType.where
                    }
                };

            var row = ModelHelper<TaskModel>.UpdateModel_By_QueryParams(attParam, task, new List<string>() { "ID", "OwnerName", "JobTypeName", "LocationName", "UpdateUserName", "CreateUserName" }, oda);

            task = GetTaskDetail(task.ID);

            var comment = task.ID != 0 ? "修改任务" : "新建任务";
            if (task.ID != 0)
            {
                var insertSql = @"insert into SMSTask(TaskID,JobNo,JobId,CycleId,TaskName,Status,OwnerID,SMSContent,CreateType,CreateTime,TelNumber,From_User,To_User)";
                var selectSql = "";

                if (oldTask.Status != task.Status)
                { //状态更新
                    comment += string.Format("[任务状态更新：{0}-->{1}]", EnumDataSource<EnumUtility.TaskStatus>.GetDisplayValue((int)oldTask.Status, "zh-cn"), EnumDataSource<EnumUtility.TaskStatus>.GetDisplayValue((int)task.Status, "zh-cn"));

                    //任务完成时 责任人发送短信给创建人
                    if (task.Status == (int)EnumUtility.TaskStatus.Completed)
                    {
                        selectSql = @"
                            select a.id,a.jobno,a.jobid,a.cycleid,a.taskname,0,a.ownerid,'任务完成：'||a.jobno||',责任人：'|| b.displayname||',完成时间：'|| to_char(a.completetime,'YYYY-MM-DD HH24:MI:SS') ||',摘要：' ||a.taskname,2,sysdate,c.phone,a.ownerid,a.createuserid from task a
                            left join user_x b on b.id = a.ownerid --责任人
                            left join user_x d on d.id = a.createuserid --创建人
                            left join accountcopy@selfservice c on d.name = c.accountid
                            where a.id = 
                        " + task.ID;

                        //ModelHelper<SMSTaskModel>.CreateModel_By_DirectSql(insertSql + selectSql, oda);
                    }
                }

                if (oldTask.OwnerID != task.OwnerID)
                { //责任人更新
                    comment += string.Format("[责任人更新：{0}-->{1}]", oldTask.OwnerID, task.OwnerID);

                    //责任人转派 责任人发送短信给新的责任人
                    if (task.Status == (int)EnumUtility.TaskStatus.InProcess)
                    {
                        selectSql = @"
                            select a.id,a.jobno,a.jobid,a.cycleid,a.taskname,0,a.ownerid,'有任务转派给你：'||a.jobno||',转派人：'|| d.displayname || ',转派时间：' || to_char(a.updatetime,'YYYY-MM-DD HH24:MI:SS') ||',摘要：' ||a.taskname,2,sysdate,c.phone,a.updateuserid,a.ownerid from task a
                            left join user_x d on d.id = a.updateuserid --转派人
                            left join user_x b on b.id = a.ownerid --责任人
                            left join accountcopy@selfservice c on b.name = c.accountid
                            where a.id= 
                        " + task.ID;

                        //ModelHelper<SMSTaskModel>.CreateModel_By_DirectSql(insertSql + selectSql, oda);
                    }
                }
            }

            SaveTaskLog(task, comment);

            return row == 1;
        }

        public bool SaveTaskLog(TaskModel task, string comment)
        {
            var taskLog = new TaskLogModel()
            {
                TaskID = task.ID,
                UpdateUserID = task.UpdateUserID,
                UpdateTime = task.UpdateTime,
                Remark = comment
            };

            var row = ModelHelper<TaskLogModel>.AddModel_By_QueryParams(taskLog, new List<string>() { }, oda);

            return row == 1;
        }

        public bool UpdateJobInvalid(JobModel job)
        {
            OracleTransaction trans = oda.BeginTransaction();
            try
            {
                oda.ExecuteScalar(sqlUpdateJobInvalid, new List<OracleParameter>()
                {
                    new OracleParameter("JobID", OracleDbType.Decimal, 100, job.ID, ParameterDirection.Input),
                    new OracleParameter(){ ParameterName="Invalid",OracleDbType=OracleDbType.Decimal,Value=job.Invalid },

                });
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                return false;
            }

            return true;
        }

        public bool UpdateJob(JobModel job)
        {
            var param = new List<QueryParameter>(){
                new QueryParameter(){
                OracleDbColumnName = "ID",OracleDbColumnType = OracleDbType.Decimal,OracleDbColumnValue = job.ID,
                PredicateType = PredicateType.where,QueryOperator = QueryOperatorType.equal
                }
            };
            var row = ModelHelper<JobModel>.UpdateModel_By_QueryParams(param, job,
                new List<string>() { "ID", "CreateTime", "JobOwnerName", "TaskUserName", "FileGID" }, oda);

            return row == 1;
        }

        /// <summary>
        /// 所有责任人为当前登陆用户，状态为进行中且未过期的任务
        /// 所有责任人为当前登陆用户，状态为进行中且已过期的任务
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="isOverTime"></param>
        /// <returns></returns>
        public List<TaskModel> GetTaskByUserID(decimal userID, Nullable<int> pageIndex, Nullable<int> pageSize, out decimal totalCount, bool isOverTime = false, string sort = "ID", string order = "Descending")
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="OwnerID", OracleDbColumnType=OracleDbType.Int64,
                        OracleDbColumnValue=userID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},

                 new QueryParameter(){OracleDbColumnName="Status", OracleDbColumnType=OracleDbType.Int64,
                        OracleDbColumnValue=1,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
          
                       //     new QueryParameter(){OracleDbColumnName="OverFlag", OracleDbColumnType=OracleDbType.Int64,
                       //OracleDbColumnValue=isOverTime,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };

            totalCount = ModelHelper<TaskModel>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<TaskModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }

        /// <summary>
        /// 获取对应年月里面的待办任务
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public Dictionary<string, List<TaskModel>> GetPendingTaskByYearAndMonth(decimal UserID, int year, int month)
        {
            var result = new Dictionary<string, List<TaskModel>>();
            var monthDays = DateTime.DaysInMonth(year, month);

            for (var i = 1; i <= monthDays; i++)
            {
                var date = new DateTime(year, month, i).Date;
                var endTime = new DateTime(year, month, i).Date;
                if (i == monthDays)
                {
                    month = month + 1;
                    endTime = new DateTime(year, month, 1).Date;
                }
                else
                {
                    endTime = new DateTime(year, month, i + 1).Date;
                }

                List<QueryParameter> attParam = new List<QueryParameter>() { 
                        new QueryParameter(){OracleDbColumnName="BeginDate", OracleDbColumnType=OracleDbType.Date,
                        OracleDbColumnValue=date,QueryOperator=QueryOperatorType.bigger,PredicateType=PredicateType.where},

                        new QueryParameter(){OracleDbColumnName="BeginDate", OracleDbColumnType=OracleDbType.Date,
                        OracleDbColumnValue=endTime,QueryOperator=QueryOperatorType.smallerNotEqual,PredicateType=PredicateType.where},

                        new QueryParameter(){OracleDbColumnName="OwnerID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=UserID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},

                        new QueryParameter(){OracleDbColumnName="Status", OracleDbColumnType=OracleDbType.Int64,
                        OracleDbColumnValue=1,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
          
                    };

                var list = ModelHelper<TaskModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { }, null, null, oda);

                result.Add(date.ToShortDateString(), list);
            }

            return result;
        }

        /// <summary>
        /// 根据任务位置获取过期的任务(最近一个月的，前后30天)
        /// </summary>
        /// <param name="Location"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalCount"></param>
        /// <param name="isOverTime"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<TaskModel> GetTaskByLocation(decimal Location, Nullable<int> pageIndex, Nullable<int> pageSize, out decimal totalCount, bool isOverTime = true, string sort = "ID", string order = "Descending")
        {
            var today = DateTime.Today;
            var beforeDay = today.AddDays(-30);
            var afterDay = today.AddDays(30);

            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="Location", OracleDbColumnType=OracleDbType.Int64,
                        OracleDbColumnValue=Location,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},

                 new QueryParameter(){OracleDbColumnName="Status", OracleDbColumnType=OracleDbType.Int64,
                        OracleDbColumnValue=1,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
          
                            new QueryParameter(){OracleDbColumnName="OverFlag", OracleDbColumnType=OracleDbType.Int64,
                       OracleDbColumnValue=isOverTime,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},

                       new QueryParameter(){OracleDbColumnName="EndDate", OracleDbColumnType=OracleDbType.Date,
                       OracleDbColumnValue=beforeDay,QueryOperator=QueryOperatorType.bigger,PredicateType=PredicateType.where},

                       new QueryParameter(){OracleDbColumnName="EndDate", OracleDbColumnType=OracleDbType.Date,
                       OracleDbColumnValue=afterDay,QueryOperator=QueryOperatorType.smaller,PredicateType=PredicateType.where},
                    };

            totalCount = ModelHelper<TaskModel>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<TaskModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }

        /// <summary>
        /// 获取某个地市今天新增的任务
        /// </summary>
        /// <param name="Location"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalCount"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<TaskModel> GetTaskCreateInToday(decimal Location, int? pageIndex, int? pageSize, out decimal totalCount, string sort = "ID", string order = "Descending")
        {
            var today = DateTime.Today;
            var tomorrow = DateTime.Today.AddDays(1);

            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="Location", OracleDbColumnType=OracleDbType.Int64,
                        OracleDbColumnValue=Location,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},

                new QueryParameter(){OracleDbColumnName="CreateTime", OracleDbColumnType=OracleDbType.Date,
                        OracleDbColumnValue=today,QueryOperator=QueryOperatorType.bigger,PredicateType=PredicateType.where},

                        new QueryParameter(){OracleDbColumnName="CreateTime", OracleDbColumnType=OracleDbType.Date,
                        OracleDbColumnValue=tomorrow,QueryOperator=QueryOperatorType.smallerNotEqual,PredicateType=PredicateType.where},
                    };

            totalCount = ModelHelper<TaskModel>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<TaskModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }

        /// <summary>
        /// 获取直到今天还未关闭的任务
        /// </summary>
        /// <param name="Location"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalCount"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<TaskModel> GetTaskNotComplete(decimal Location, int? pageIndex, int? pageSize, out decimal totalCount, string sort = "ID", string order = "Descending")
        {
            var today = DateTime.Today;

            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="Location", OracleDbColumnType=OracleDbType.Int64,
                        OracleDbColumnValue=Location,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},

                new QueryParameter(){OracleDbColumnName="Status", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=Wicresoft.UnifyShow.Utility.EnumUtility.TaskStatus.Closed,
                        QueryOperator=QueryOperatorType.notEqual,PredicateType=PredicateType.where},

                        new QueryParameter(){OracleDbColumnName="CreateTime", OracleDbColumnType=OracleDbType.Date,
                        OracleDbColumnValue=today,QueryOperator=QueryOperatorType.smallerNotEqual,PredicateType=PredicateType.where},
                    };

            totalCount = ModelHelper<TaskModel>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<TaskModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }

        /// <summary>
        /// 所有创建人为当前登陆用户，状态为已完成的任务
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public List<TaskModel> GetTaskByCreateUserID(decimal userID, int? pageIndex, int? pageSize, out decimal totalCount, string sort = "JobNo", string order = "Descending")
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="CreateUserID", OracleDbColumnType=OracleDbType.Int64,
                        OracleDbColumnValue=userID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},

                 new QueryParameter(){OracleDbColumnName="Status", OracleDbColumnType=OracleDbType.Int64,
                        OracleDbColumnValue=2,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                      };
            totalCount = ModelHelper<TaskModel>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<TaskModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }

        /// <summary>
        /// 当前登陆人创建的计划列表
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public List<JobModel> GetPlanByUserID(decimal userID, int? pageIndex, int? pageSize, out decimal totalCount, string sort = "ID", string order = "Descending")
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="JobOwnerID", OracleDbColumnType=OracleDbType.Int64,
                        OracleDbColumnValue=userID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                      };
            totalCount = ModelHelper<JobModel>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<JobModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }

        /// <summary>
        /// 计划所对应的任务列表
        /// </summary>
        /// <param name="jobID"></param>
        /// <returns></returns>
        public List<TaskModel> GetTaskByJobID(decimal jobID, int? pageIndex, int? pageSize, out decimal totalCount, string sort = "JobNo", string order = "Descending")
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="jobID", OracleDbColumnType=OracleDbType.Int64,
                        OracleDbColumnValue=jobID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                      };
            totalCount = ModelHelper<TaskModel>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<TaskModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }


        /// <summary>
        /// 保存第三方巡检类型
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public bool SaveInspectionType(InspectionType type)
        {
            var row = ModelHelper<InspectionType>.AddModel_By_QueryParams(type, new List<string>() { "CreatorName" }, oda);

            return row == 1;
        }

        /// <summary>
        /// 获取所有的第三方巡检类型
        /// </summary>
        /// <param name="totalCount">总个数</param>
        /// <param name="pageIndex">从1开始</param>
        /// <param name="pageSize">每页数据条数</param>
        /// <param name="sort">排序字段</param>
        /// <param name="order">Ascending,Descending</param>
        /// <returns></returns>
        public List<InspectionType> GetAllInspectionType(out decimal totalCount, Nullable<int> pageIndex, Nullable<int> pageSize, string sort = "ID", string order = "Descending")
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);

            List<QueryParameter> attParam = new List<QueryParameter>() { };

            totalCount = ModelHelper<InspectionType>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<InspectionType>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }


        public bool DeleteInspectionType(decimal ID)
        {
            int result = oda.ExecuteCommand(sqlDeleteInspectionType, new List<OracleParameter>()
                {   
                    new OracleParameter()
                    {
                        ParameterName="id",
                        OracleDbType = OracleDbType.Decimal,
                        Value = ID
                    }
                });

            return result == 1;
        }

        #region 业务系统搜索
        public List<T> SearchInspectionObject<T>(out decimal totalCount, InspectionObjectType type, string company, QueryString query, int? pageIndex, int? pageSize, string sort = "Request_ID", string order = "Descending")
        {
            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                var selectColumn = string.Empty;
                var props = typeof(T).GetProperties();
                foreach (var p in props)
                {
                    selectColumn += p.Name + ',';
                }

                var orderName = " asc ";
                if (!string.IsNullOrWhiteSpace(order))
                {
                    if (order.Equals("DESC", StringComparison.OrdinalIgnoreCase) ||
                        order.Equals("DESCENDING", StringComparison.OrdinalIgnoreCase))
                    {
                        orderName = " desc ";
                    }
                }

                int beginIndex = (pageIndex.Value - 1) * pageSize.Value + 1;
                int endIndex = beginIndex + pageSize.Value - 1;

                var condition = MakeUpCondition(query, type);

                if (string.IsNullOrWhiteSpace(sort)) sort = "Request_ID";

                if (!string.IsNullOrWhiteSpace(company))
                {
                    condition += string.Format(" and company='{0}'", company);
                }

                condition += " order by " + sort + " " + orderName;

                var sqlFormat = sqlSearchInspObject_Business;

                switch (type)
                {
                    case InspectionObjectType.Business:
                        sqlFormat = sqlSearchInspObject_Business;
                        break;
                    case InspectionObjectType.Server:
                        sqlFormat = sqlSearchInspObject_Server;
                        break;
                    case InspectionObjectType.Net:
                        sqlFormat = sqlSearchInspObject_Net;
                        break;
                    case InspectionObjectType.Safety:
                        sqlFormat = sqlSearchInspObject_Safety;
                        break;
                    case InspectionObjectType.DB:
                        sqlFormat = sqlSearchInspObject_Db;
                        break;
                    case InspectionObjectType.Storage:
                        sqlFormat = sqlSearchInspObject_Storage;
                        break;
                    case InspectionObjectType.MiddleWare:
                        sqlFormat = sqlSearchInspObject_Middle;
                        break;
                }

                var countSql = string.Format(sqlFormat, "count(*)", condition, "");

                var sql = string.Format(sqlFormat, selectColumn.TrimEnd(','), condition, " where rn between " + beginIndex + " and " + endIndex + "");

                var list = ModelHelper<T>.GetModel_By_DirectSql(sql, null, conitsm);

                totalCount = ModelHelper<T>.GetModelCount_By_DirectSql(countSql, null, conitsm);

                return list;

            }
        }

        /// <summary>
        /// 拼凑业务系统的查询条件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        private string MakeUpCondition(QueryString query, InspectionObjectType type)
        {
            var condition = string.Empty;
            switch (type)
            {
                //按照设备名称或者IP搜索或者用途搜索
                case InspectionObjectType.Server:
                    if (!string.IsNullOrWhiteSpace(query.Key))
                    {
                        condition += string.Format(" and (upper(DeviceName) like '%{0}%' or upper(IPAddress) like '%{0}%' or upper(Usaged) like '%{0}%') ", query.Key.ToUpper());
                    }
                    if (!string.IsNullOrWhiteSpace(query.ServerType))
                    {
                        condition += string.Format(" and (upper(Item) like '%{0}%') ", query.ServerType.ToUpper());
                    }
                    break;
                case InspectionObjectType.MiddleWare:
                    if (!string.IsNullOrWhiteSpace(query.Key))
                    {
                        condition += string.Format(" and (upper(DeviceName) like '%{0}%' or upper(IPAddress) like '%{0}%' or upper(Usaged) like '%{0}%') ", query.Key.ToUpper());
                    }
                    if (!string.IsNullOrWhiteSpace(query.ServerType))
                    {
                        condition += string.Format(" and (upper(MidAndVersion) like '%{0}%') ", query.ServerType.ToUpper());
                    }
                    break;
                case InspectionObjectType.DB:
                    if (!string.IsNullOrWhiteSpace(query.Key))
                    {
                        condition += string.Format(" and (upper(DeviceName) like '%{0}%' or upper(IPAddress) like '%{0}%' or upper(Usaged) like '%{0}%') ", query.Key.ToUpper());
                    }
                    if (!string.IsNullOrWhiteSpace(query.ServerType))
                    {
                        condition += string.Format(" and (upper(DBAndVersion) like '%{0}%') ", query.ServerType.ToUpper());
                    }
                    break;
                //按照设备名称或者IP搜索
                case InspectionObjectType.Business:
                    if (!string.IsNullOrWhiteSpace(query.Key))
                    {
                        condition += string.Format(" and (upper(AppSystemName) like '%{0}%' or upper(IPAddress) like '%{0}%') ", query.Key.ToUpper());
                    }
                    break;
                case InspectionObjectType.Net:
                case InspectionObjectType.Safety:
                    if (!string.IsNullOrWhiteSpace(query.Key))
                    {
                        condition += string.Format(" and (upper(DeviceName) like '%{0}%' or upper(IPAddress) like '%{0}%') ", query.Key.ToUpper());
                    }
                    if (!string.IsNullOrWhiteSpace(query.ServerType))
                    {
                        condition += string.Format(" and (upper(ManufacturerName) like '%{0}%') ", query.ServerType.ToUpper());
                    }
                    break;
                //按照IP搜索
                case InspectionObjectType.Storage:
                    if (!string.IsNullOrWhiteSpace(query.Key))
                    {
                        condition += string.Format(" and (upper(IPAddress) like '%{0}%') ", query.Key.ToUpper());
                    }
                    break;
            }

            //过滤重复项
            if (query.ExcludeIDList != null && query.ExcludeIDList.Count > 0)
            {
                var exclueString = string.Empty;
                foreach (var item in query.ExcludeIDList)
                {
                    exclueString += string.Format("'{0}',", item);
                }
                condition += " and Request_ID not in (" + exclueString.TrimEnd(',') + ") ";
            }

            return condition;
        }

        #endregion

        /// <summary>
        /// 获取所有电子公告
        /// </summary>
        /// <param name="totalCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<AnnouncementModel> GetAllAnnouncement(out decimal totalCount, int? pageIndex, int? pageSize, bool filterOverTime = false, string sort = "CreateTime", string order = "Descending")
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);

            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){
                    OracleDbColumnName = "InValid",
                    OracleDbColumnType = OracleDbType.Decimal,
                    PredicateType = PredicateType.where,
                    OracleDbColumnValue = 1,
                    QueryOperator = QueryOperatorType.equal
                }
            };

            if (filterOverTime)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "EndTime",
                    OracleDbColumnType = OracleDbType.Date,
                    PredicateType = PredicateType.where,
                    OracleDbColumnValue = DateTime.Today,
                    QueryOperator = QueryOperatorType.bigger
                });
            }

            totalCount = ModelHelper<AnnouncementModel>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<AnnouncementModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }

        /// <summary>
        /// 删除电子公告
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool DeleteAnnouncement(decimal ID)
        {
            int result = oda.ExecuteCommand(sqlDeleteAnnouncement, new List<OracleParameter>()
                {   
                    new OracleParameter()
                    {
                        ParameterName="id",
                        OracleDbType = OracleDbType.Decimal,
                        Value = ID
                    }
                });

            return result == 1;
        }

        /// <summary>
        /// 添加电子公告
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool CreateAnnouncement(AnnouncementModel model)
        {
            var row = ModelHelper<AnnouncementModel>.AddModel_By_QueryParams(model, new List<string>() { "CreateByName" }, oda);

            return row == 1;
        }

        public bool UpdateAnnouncement(AnnouncementModel model)
        {
            OracleTransaction trans = oda.BeginTransaction();
            try
            {
                oda.ExecuteScalar(sqlUpdateAnnouncement, new List<OracleParameter>()
                {
                    new OracleParameter("id", OracleDbType.Decimal, 100, model.ID, ParameterDirection.Input),
                    new OracleParameter(){ ParameterName="Title",OracleDbType=OracleDbType.NVarchar2,Value=model.Title },
                    new OracleParameter(){ ParameterName="Content",OracleDbType=OracleDbType.NVarchar2,Value=model.Content },
                    new OracleParameter(){ ParameterName="BeginTime",OracleDbType=OracleDbType.Date,Value=model.BeginTime },
                    new OracleParameter(){ ParameterName="EndTime",OracleDbType=OracleDbType.Date,Value=model.EndTime },
                });
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                return false;
            }

            return true;
        }

        public AnnouncementModel GetAnnouncementDetail(int ID)
        {
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){
                    OracleDbColumnName = "ID",
                    OracleDbColumnType = OracleDbType.Decimal,
                    PredicateType = PredicateType.where,
                    OracleDbColumnValue = (decimal)ID,
                    QueryOperator = QueryOperatorType.equal
                }
            };

            return ModelHelper<AnnouncementModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { }, null, null, oda).FirstOrDefault();
        }

        public List<JobViewModel> SearchJob(SearchParam param, out decimal totalCount, int? pageIndex, int? pageSize, string sort = "ID", string order = "Descending")
        {
            var ret = new List<JobViewModel>();
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>();
            JoinCauses joins = new JoinCauses();
            //JoinCause c1 = new JoinCause()
            //{
            //    Alia = "a",
            //    TableName = "Job_x",
            //    Type = JoinType.left
            //};
            //if (param.TaskStatus != -1)
            //{
            //    attParam.Add(new QueryParameter()
            //                {
            //                    OracleDbColumnName = "Status",
            //                    OracleDbColumnType = OracleDbType.Int64,
            //                    OracleDbColumnValue = param.TaskStatus,
            //                    QueryOperator = QueryOperatorType.equal,
            //                    PredicateType = PredicateType.where
            //                });
            //}
            if (param.TaskType != -1)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "JobType",
                    OracleDbColumnType = OracleDbType.Int64,
                    OracleDbColumnValue = param.TaskType,
                    QueryOperator = QueryOperatorType.equal,
                    PredicateType = PredicateType.where
                });
            }
            if (param.CreatorLocation != 0)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "CreatorLocation",
                    OracleDbColumnType = OracleDbType.Decimal,
                    OracleDbColumnValue = param.CreatorLocation,
                    QueryOperator = QueryOperatorType.equal,
                    PredicateType = PredicateType.where
                });
            }
            if (param.TaskRange != -1)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "JobRange",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    OracleDbColumnValue = string.Format("{0};", param.TaskRange),
                    QueryOperator = QueryOperatorType.like,
                    PredicateType = PredicateType.where
                });
            }

            if (param.CreatorID != 0)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "JobOwnerID",
                    OracleDbColumnType = OracleDbType.Decimal,
                    OracleDbColumnValue = param.CreatorID,
                    QueryOperator = QueryOperatorType.equal,
                    PredicateType = PredicateType.where
                });
            }

            if (param.PlanStatus != -1)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "Status",
                    OracleDbColumnType = OracleDbType.Decimal,
                    OracleDbColumnValue = param.PlanStatus,
                    QueryOperator = QueryOperatorType.equal,
                    PredicateType = PredicateType.where
                });
            }
            //c1.AddOnQuery(new OnQueryParameter()
            //{
            //    AliaLeft = "a",
            //    AliaRight = "b",
            //    ColumnNameLeft = "ID",
            //    ColumnNameRight = "JobID"
            //});


            //JoinCause c2 = new JoinCause();
            //c2.Alia = "b";
            //c2.TableName = "Cycle";
            //c2.Type = JoinType.inner;
            //if (param.StartDate.HasValue)
            //{
            //    c2.AddQueryParameter(new QueryParameter()
            //    {
            //        OracleDbColumnName = "BeginDate",
            //        OracleDbColumnType = OracleDbType.Date,
            //        OracleDbColumnValue = param.StartDate.Value,
            //        QueryOperator = QueryOperatorType.bigger,
            //        PredicateType = PredicateType.where
            //    });
            //}
            //if (param.EndDate.HasValue)
            //{
            //    c2.AddQueryParameter(new QueryParameter()
            //    {
            //        OracleDbColumnName = "EndDate",
            //        OracleDbColumnType = OracleDbType.Date,
            //        OracleDbColumnValue = param.EndDate.Value,
            //        QueryOperator = QueryOperatorType.smaller,
            //        PredicateType = PredicateType.where
            //    });
            //}
            //c2.AddOnQuery(new OnQueryParameter()
            //{
            //    AliaLeft = "a",
            //    AliaRight = "b",
            //    ColumnNameLeft = "ID",
            //    ColumnNameRight = "JobID"
            //});
            //joins.Add(c2);

            totalCount = ModelHelper<JobViewModel>.GetModelCount_By_QueryParams(attParam, oda, joins);
            ret = ModelHelper<JobViewModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda, joins);
            return ret;
        }

        public List<TaskModel> SearchTask(SearchParam param, out decimal totalCount, int? pageIndex, int? pageSize, string sort = "ID", string order = "Descending")
        {
            var ret = new List<TaskModel>();
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>();

            if (param.TaskType != -1)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "JobType",
                    OracleDbColumnType = OracleDbType.Int64,
                    OracleDbColumnValue = param.TaskType,
                    QueryOperator = QueryOperatorType.equal,
                    PredicateType = PredicateType.where
                });
            }
            if (param.TaskRange != -1)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "Location",
                    OracleDbColumnType = OracleDbType.Int64,
                    OracleDbColumnValue = param.TaskRange,
                    QueryOperator = QueryOperatorType.equal,
                    PredicateType = PredicateType.where
                });
            }
            if (param.TaskStatus != -1)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "Status",
                    OracleDbColumnType = OracleDbType.Decimal,
                    OracleDbColumnValue = param.TaskStatus,
                    QueryOperator = QueryOperatorType.equal,
                    PredicateType = PredicateType.where
                });
            }

            var searchDateName = param.SearchDateType == 1 ? "BeginDate" : "EndDate";
            if (param.StartDate.HasValue)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = searchDateName,
                    OracleDbColumnType = OracleDbType.Date,
                    OracleDbColumnValue = param.StartDate.Value,
                    QueryOperator = QueryOperatorType.bigger,
                    PredicateType = PredicateType.where
                });
            }
            if (param.EndDate.HasValue)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = searchDateName,
                    OracleDbColumnType = OracleDbType.Date,
                    OracleDbColumnValue = param.EndDate.Value,
                    QueryOperator = QueryOperatorType.smaller,
                    PredicateType = PredicateType.where
                });
            }

            if (param.ResponsibleID != 0)
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "OwnerID",
                    OracleDbColumnType = OracleDbType.Decimal,
                    OracleDbColumnValue = (decimal)param.ResponsibleID,
                    QueryOperator = QueryOperatorType.equal,
                    PredicateType = PredicateType.where
                });
            }

            totalCount = ModelHelper<TaskModel>.GetModelCount_By_QueryParams(attParam, oda);
            ret = ModelHelper<TaskModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
            return ret;
        }


        public List<string> GetVendorList()
        {
            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                var list = ModelHelper<Vendor>.GetModel_By_DirectSql(sqlGetVendorList, null, conitsm);

                return list.Select(x => x.vendor).ToList();
            }
            //return new List<string>() { "1" };
        }

        public List<string> GetDBVersionList()
        {
            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                var list = ModelHelper<DBVersion>.GetModel_By_DirectSql(sqlGetDBVersionList, null, conitsm);

                return list.Select(x => x.databaseandversion).ToList();
            }
            //return new List<string>() { "1" };
        }

        public List<string> GetMiddleVersionList()
        {
            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                var list = ModelHelper<MiddleVersion>.GetModel_By_DirectSql(sqlGetMiddleVersionList, null, conitsm);

                return list.Select(x => x.midsoftwareandversion).ToList();
            }
            //return new List<string>() { "1" };
        }

        public List<string> GetVersionList()
        {
            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                var list = ModelHelper<AllVersion>.GetModel_By_DirectSql(sqlGetVersionList, null, conitsm);

                return list.Select(x => x.VersionNumber).ToList();
            }
            //return new List<string>() { "1" };
        }

        public List<string> GetUsingList()
        {

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                var list = ModelHelper<AppSystem>.GetModel_By_DirectSql(sqlGetUsingList, null, conitsm);

                return list.Select(x => x.AppSystemName).ToList();
            }

            //return new List<string>() { "1" };
        }

        public PlanLocationOwner GetInterfacePeopleByLocationID(decimal locationID)
        {
            var attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName = "LOCATIONID",OracleDbColumnType = OracleDbType.Decimal,
                OracleDbColumnValue = locationID,PredicateType = PredicateType.where,QueryOperator = QueryOperatorType.equal}
            };
            return ModelHelper<PlanLocationOwner>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { }, null, null, oda).FirstOrDefault();
        }

        public bool SaveInterfacePeople(decimal ownerID, decimal LocationID)
        {
            var owner = GetInterfacePeopleByLocationID(LocationID);
            if (owner == null)
            {
                var user = new User_X();
                var location = user.GetSiteBranchBySiteID(LocationID);

                var model = new PlanLocationOwner()
                {
                    LocationID = LocationID,
                    OwnerID = ownerID,
                    Alias = location.Alias,
                    OwnerIDBak = ownerID
                };
                var rows = ModelHelper<PlanLocationOwner>.AddModel_By_QueryParams(model, new List<string>() { "ID", "OwnerName", "LocationName" }, oda);
                return rows == 1;
            }

            var param = new List<QueryParameter>(){
                new QueryParameter(){
                    OracleDbColumnName="LocationID",OracleDbColumnType = OracleDbType.Decimal,
                    OracleDbColumnValue = LocationID,PredicateType = PredicateType.where,
                    QueryOperator = QueryOperatorType.equal
                }
            };
            owner.OwnerID = ownerID;
            owner.OwnerIDBak = ownerID;
            var row = ModelHelper<PlanLocationOwner>.UpdateModel_By_QueryParams(param, owner, new List<string>() { "ID", "OwnerName", "LocationName" }, oda);
            return row == 1;
        }

        /// <summary>
        /// 获取所在地市的所有计划任务组
        /// </summary>
        /// <param name="locationID"></param>
        /// <param name="isGlobalAdmin">省局管理员可以查看所有的内置计划组</param>
        /// <param name="totalCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<PlanGroupModel> GetAllGroup(decimal locationID, bool isGlobalAdmin, out decimal totalCount, int? pageIndex, int? pageSize, string sort = "ID", string order = "Descending")
        {
            var selectColumn = string.Empty;
            var props = typeof(PlanGroupModel).GetProperties();
            foreach (var p in props)
            {
                if (p.Name.Equals("CreatorName", StringComparison.OrdinalIgnoreCase)) break;
                selectColumn += p.Name + ',';
            }

            var orderName = " asc ";
            if (!string.IsNullOrWhiteSpace(order))
            {
                if (order.Equals("DESC", StringComparison.OrdinalIgnoreCase) ||
                    order.Equals("DESCENDING", StringComparison.OrdinalIgnoreCase))
                {
                    orderName = " desc ";
                }
            }
            var condition = string.Empty;
            if (isGlobalAdmin)
            {
                condition += string.Format(" (grouptype=1 or (grouptype=2 and locationid={0})) ", locationID);
            }
            else
            {
                condition += string.Format(" locationid={0} ", locationID);
            }

            condition += " order by " + sort + " " + orderName;

            var countSql = string.Format(sqlGetGlobalAllGroup, "count(*)", condition, "");
            var sql = string.Empty;
            if (pageIndex.HasValue && pageSize.HasValue)
            {
                int beginIndex = (pageIndex.Value - 1) * pageSize.Value + 1;
                int endIndex = beginIndex + pageSize.Value - 1;

                sql = string.Format(sqlGetGlobalAllGroup, selectColumn.TrimEnd(','), condition, " where rn between " + beginIndex + " and " + endIndex + "");
            }
            else
            {
                sql = string.Format(sqlGetGlobalAllGroup, selectColumn.TrimEnd(','), condition, "");
            }

            totalCount = ModelHelper<PlanGroupModel>.GetModelCount_By_DirectSql(countSql, null, oda);

            return ModelHelper<PlanGroupModel>.GetModel_By_DirectSql(sql, null, oda);

        }

        /// <summary>
        /// 根据组名获取计划组
        /// </summary>
        /// <param name="groupName"></param>
        /// <returns></returns>
        public List<PlanGroupModel> GetGroupByName(string groupName, decimal locationID)
        {
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="Invalid", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=1,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                new QueryParameter(){OracleDbColumnName="LocationID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=locationID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                 new QueryParameter(){OracleDbColumnName="GroupName", OracleDbColumnType=OracleDbType.NVarchar2,
                        OracleDbColumnValue=groupName,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };

            return ModelHelper<PlanGroupModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { }, null, null, oda);
        }

        public PlanGroupModel GetGroupByID(decimal groupID)
        {
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="Invalid", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=1,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},

                 new QueryParameter(){OracleDbColumnName="ID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=groupID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };

            return ModelHelper<PlanGroupModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { }, null, null, oda).FirstOrDefault();
        }

        /// <summary>
        /// 新建计划任务组(组名唯一)
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool SavePlanGroup(PlanGroupModel model)
        {
            var group = GetGroupByName(model.GroupName, model.LocationID);
            if (group.Count > 0) return false;

            var row = ModelHelper<PlanGroupModel>.AddModel_By_QueryParams(model, new List<string>() { "CreatorName" }, oda);
            return row == 1;
        }

        /// <summary>
        /// 更新任务组
        /// </summary>
        /// <param name="groupID"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdatePlanGroup(PlanGroupModel model)
        {
            var param = new List<QueryParameter>(){
                new QueryParameter(){
                OracleDbColumnName = "ID",OracleDbColumnType = OracleDbType.Decimal,OracleDbColumnValue = model.ID,
                PredicateType = PredicateType.where,QueryOperator = QueryOperatorType.equal
                }
            };
            var row = ModelHelper<PlanGroupModel>.UpdateModel_By_QueryParams(param, model, new List<string>() { "ID", "CreatorName" }, oda);

            return row == 1;
        }

        public List<PlanGroupUserModel> GetUsersByGroup(decimal groupID, out decimal totalCount, int? pageIndex, int? pageSize, string sort = "ID", string order = "Descending")
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="GroupID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=groupID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };

            totalCount = ModelHelper<PlanGroupUserModel>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<PlanGroupUserModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }

        public PlanGroupUserModel GetUserInGroup(decimal userID, decimal groupID)
        {
            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="GroupID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=groupID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                new QueryParameter(){OracleDbColumnName="UserID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=userID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };
            return ModelHelper<PlanGroupUserModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { }, null, null, oda).FirstOrDefault();
        }

        public bool AddUserIntoGroup(decimal groupID, decimal userID)
        {
            var user = GetUserInGroup(userID, groupID);
            if (user == null)
            {
                var model = new PlanGroupUserModel()
                {
                    GroupID = groupID,
                    UserID = userID
                };

                var row = ModelHelper<PlanGroupUserModel>.AddModel_By_QueryParams(model, new List<string>() { "GroupName", "UserName", "Company", "Department", "Position" }, oda);
                return row == 1;
            }

            return true;
        }

        public bool AddUserListIntoGroup(decimal groupID, List<decimal> users)
        {
            var flag = true;
            foreach (var user in users)
            {
                flag &= AddUserIntoGroup(groupID, user);
            }
            return flag;
        }

        public bool DeleteUserFromGroup(decimal groupID, decimal userID)
        {
            int result = oda.ExecuteCommand(sqlDeleteGroupUser, new List<OracleParameter>()
                {   
                    new OracleParameter()
                    {
                        ParameterName="groupid",
                        OracleDbType = OracleDbType.Decimal,
                        Value = groupID
                    },
                    new OracleParameter()
                    {
                        ParameterName="userid",
                        OracleDbType = OracleDbType.Decimal,
                        Value = userID
                    }
                });

            return result == 1;
        }

        /// <summary>
        /// 初始化内置计划任务组的成员
        /// </summary>
        /// <returns></returns>
        public bool SyncInternalGroupUser()
        {
            var result = false;

            OracleTransaction trans = oda.BeginTransaction();

            try
            {
                //删除内置任务组的成员
                int deleted = oda.ExecuteCommand(sqlDeleteInternalPlanGroupUser);

                //初始化各地市的信息中心人员进入内置任务组
                oda.ExecuteCommand(sqlInsertFJInternalUser);
                oda.ExecuteCommand(sqlInsertXMInetrnalUser);
                oda.ExecuteCommand(sqlInsertFZHInetrnalUser);
                oda.ExecuteCommand(sqlInsertZZHInternalUser);
                oda.ExecuteCommand(sqlInsertQZHInternalUser);
                oda.ExecuteCommand(sqlInsertPTInternalUser);
                oda.ExecuteCommand(sqlInsertNDInternalUser);
                oda.ExecuteCommand(sqlInsertNPInternalUser);
                oda.ExecuteCommand(sqlInsertSMInternalUser);
                oda.ExecuteCommand(sqlInsertLYInternalUser);
                oda.ExecuteCommand(sqlInsertHTInternalUser);
                oda.ExecuteCommand(sqlInsertSMJYInternalUser);
                oda.ExecuteCommand(sqlInsertJCKInternalUser);
                oda.ExecuteCommand(sqlInsertWYYYInetrnalUser);

                result = true;
                trans.Commit();

            }
            catch (Exception e)
            {
                trans.Rollback(); return false;
            }

            return result;
        }

    }
}
